/*DONE* Table [dbo].[<CompanyName>$Value Entry$437dbf0e-84ff-417a-965d-ed2bb9650972]  1/2  Script Date: 30.7.2020 - JI ******/
/*Add prestaging table for one specific company*/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

DECLARE @CreateTable as VARCHAR(MAX)
DECLARE @Company as VARCHAR(100)
DECLARE @CompanyID as INT

SET @CompanyID = X --The X is the value that needs to be replaced with the [Company] ID from LSInsight$Companies
SET @Company = (SELECT LSInsight$Companies.[CompanyPrefix] from dbo.LSInsight$Companies WHERE [Company] = @CompanyID)

IF @Company IS NOT NULL
BEGIN

DECLARE @DropTable as VARCHAR(MAX)
SET @DropTable = 'DROP TABLE IF EXISTS [dbo].['+@Company+'$Value Entry$437dbf0e-84ff-417a-965d-ed2bb9650972]'
EXEC(@DropTable)

SET @CreateTable = 'CREATE TABLE [dbo].['+@Company+'$Value Entry$437dbf0e-84ff-417a-965d-ed2bb9650972](
	[timestamp] [timestamp] NOT NULL,
	[Entry No_] [int] NOT NULL,
	[Item No_] [nvarchar](20) NOT NULL,
	[Posting Date] [datetime] NOT NULL,
	[Item Ledger Entry Type] [int] NOT NULL,
	[Source No_] [nvarchar](20) NOT NULL,
	[Document No_] [nvarchar](20) NOT NULL,
	[Description] [nvarchar](100) NOT NULL,
	[Location Code] [nvarchar](10) NOT NULL,
	[Inventory Posting Group] [nvarchar](20) NOT NULL,
	[Source Posting Group] [nvarchar](20) NOT NULL,
	[Item Ledger Entry No_] [int] NOT NULL,
	[Valued Quantity] [decimal](38, 20) NOT NULL,
	[Item Ledger Entry Quantity] [decimal](38, 20) NOT NULL,
	[Invoiced Quantity] [decimal](38, 20) NOT NULL,
	[Cost per Unit] [decimal](38, 20) NOT NULL,
	[Sales Amount (Actual)] [decimal](38, 20) NOT NULL,
	[Salespers__Purch_ Code] [nvarchar](20) NOT NULL,
	[Discount Amount] [decimal](38, 20) NOT NULL,
	[User ID] [nvarchar](50) NOT NULL,
	[Source Code] [nvarchar](10) NOT NULL,
	[Applies-to Entry] [int] NOT NULL,
	[Global Dimension 1 Code] [nvarchar](20) NOT NULL,
	[Global Dimension 2 Code] [nvarchar](20) NOT NULL,
	[Source Type] [int] NOT NULL,
	[Cost Amount (Actual)] [decimal](38, 20) NOT NULL,
	[Cost Posted to G_L] [decimal](38, 20) NOT NULL,
	[Reason Code] [nvarchar](10) NOT NULL,
	[Drop Shipment] [tinyint] NOT NULL,
	[Journal Batch Name] [nvarchar](10) NOT NULL,
	[Gen_ Bus_ Posting Group] [nvarchar](20) NOT NULL,
	[Gen_ Prod_ Posting Group] [nvarchar](20) NOT NULL,
	[Document Date] [datetime] NOT NULL,
	[External Document No_] [nvarchar](35) NOT NULL,
	[Cost Amount (Actual) (ACY)] [decimal](38, 20) NOT NULL,
	[Cost Posted to G_L (ACY)] [decimal](38, 20) NOT NULL,
	[Cost per Unit (ACY)] [decimal](38, 20) NOT NULL,
	[Document Type] [int] NOT NULL,
	[Document Line No_] [int] NOT NULL,
	[Order Type] [int] NOT NULL,
	[Order No_] [nvarchar](20) NOT NULL,
	[Order Line No_] [int] NOT NULL,
	[Expected Cost] [tinyint] NOT NULL,
	[Item Charge No_] [nvarchar](20) NOT NULL,
	[Valued By Average Cost] [tinyint] NOT NULL,
	[Partial Revaluation] [tinyint] NOT NULL,
	[Inventoriable] [tinyint] NOT NULL,
	[Valuation Date] [datetime] NOT NULL,
	[Entry Type] [int] NOT NULL,
	[Variance Type] [int] NOT NULL,
	[Purchase Amount (Actual)] [decimal](38, 20) NOT NULL,
	[Purchase Amount (Expected)] [decimal](38, 20) NOT NULL,
	[Sales Amount (Expected)] [decimal](38, 20) NOT NULL,
	[Cost Amount (Expected)] [decimal](38, 20) NOT NULL,
	[Cost Amount (Non-Invtbl_)] [decimal](38, 20) NOT NULL,
	[Cost Amount (Expected) (ACY)] [decimal](38, 20) NOT NULL,
	[Cost Amount (Non-Invtbl_)(ACY)] [decimal](38, 20) NOT NULL,
	[Expected Cost Posted to G_L] [decimal](38, 20) NOT NULL,
	[Exp_ Cost Posted to G_L (ACY)] [decimal](38, 20) NOT NULL,
	[Dimension Set ID] [int] NOT NULL,
	[Job No_] [nvarchar](20) NOT NULL,
	[Job Task No_] [nvarchar](20) NOT NULL,
	[Job Ledger Entry No_] [int] NOT NULL,
	[Variant Code] [nvarchar](10) NOT NULL,
	[Adjustment] [tinyint] NOT NULL,
	[Average Cost Exception] [tinyint] NOT NULL,
	[Capacity Ledger Entry No_] [int] NOT NULL,
	[Type] [int] NOT NULL,
	[No_] [nvarchar](20) NOT NULL,
	[Return Reason Code] [nvarchar](10) NOT NULL,
	[$systemId] [uniqueidentifier] NOT NULL,
 CONSTRAINT ['+@Company+'$Value Entry$437dbf0e-84ff-417a-965d-ed2bb9650972$Key1] PRIMARY KEY CLUSTERED 
(
	[Entry No_] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY],
 CONSTRAINT ['+@Company+'$Value Entry$437dbf0e-84ff-417a-965d-ed2bb9650972$$systemId] UNIQUE NONCLUSTERED 
(
	[$systemId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]'

EXEC(@CreateTable)

END